#! /bin/bash
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
create database if not exists oe_dws;
-- 董曲岩
DROP TABLE if exists oe_dws.dws_teach_periodcnt;
create table if not exists oe_dws.dws_teach_periodcnt(
    --维度
    class_id string comment '班级id',
    class_date string comment '上课时间 "上课时间段"',
    period string comment '早中晚粒度，字段值格式：m（morning），a（afternoon），e（evening）',
    --指标
    normal_signin_cnt bigint comment '正常出勤人数',
    normal_signin_rate decimal(5,2) comment '正常出勤率',

    late_signin_cnt bigint comment '迟到人数',
    late_signin_rate decimal(5,2) comment '迟到率',

    leave_cnt bigint comment '请假人数',
    leave_rate decimal(5,2) comment '请假率',

    absent_cnt bigint comment '旷课人数',
    absent_rate decimal(5,2) comment '旷课率',

    dt string comment '记录'
)comment '考勤分析时间段统计'
    row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'Snappy');
;

"

PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
--董曲岩
INSERT INTO oe_dws.dws_teach_periodcnt
-- 早上正常出勤,迟到人数计算
with t1 as (select
                --维度
                class_id,
                class_date,
                student_id,
                --指标
                -- 每个班级,每个日期,每个学生,上午正常出勤时间段内打卡的次数
                sum(if(substring(signin_time, 12, 8)
                    between substring(cast(date_add('minute', -40,cast(concat('1970-01-01 ', morning_begin_time) AS timestamp)) as varchar),12, 8)
                           and substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', morning_begin_time) AS timestamp)) as varchar),12, 8), 1, 0)) as normal_signin_cnt_each_student,
                -- 每个班级,每个日期,每个学生,上午迟到时间段内打卡的次数
                sum(if(substring(signin_time, 12, 8)
                    between substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', morning_begin_time) AS timestamp)) as varchar), 12, 8)
                           and substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', morning_end_time) AS timestamp)) as varchar),12, 8), 1, 0)) as late_signin_cnt_each_student
            from oe_dwb.dwb_clock_late_detail
            where content is not null
              and content <> ''
            group by class_id, class_date, student_id),
-- 对于每个班级,每个日期,如果早上正常出勤时间内有打卡,则判定为早上正常打卡
-- 对于每个班级,每个日期,如果早上正常出勤时间内没有打卡,且迟到时间段内有打卡,则判定为早上迟到打卡
    t2 as (select
               class_id,
               class_date,
               'm' as period,
               -- 对于每个班级,每个日期,如果早上正常出勤时间内有打卡,则判定为早上正常打卡
               count(if(normal_signin_cnt_each_student > 0, 1, null)) as normal_signin_cnt,
               -- 对于每个班级,每个日期,如果早上正常出勤时间内没有打卡,且迟到时间段内有打卡,则判定为早上迟到打卡
               count(if(normal_signin_cnt_each_student = 0 and late_signin_cnt_each_student > 0, 1,null)) as late_signin_cnt
           from t1
           group by class_id, class_date),
-- 下午正常出勤,迟到人数计算
    t3 as (select
            --维度
            class_id,
            class_date,
            student_id,
            --指标
            -- 每个班级,每个日期,每个学生,上午正常出勤时间段内打卡的次数
            sum(if(substring(signin_time, 12, 8)
                between substring(cast(date_add('minute', -40,cast(concat('1970-01-01 ', afternoon_begin_time) AS timestamp)) as varchar),12, 8)
                       and substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', afternoon_begin_time) AS timestamp)) as varchar),12, 8), 1, 0)) as normal_signin_cnt_each_student,
            -- 每个班级,每个日期,每个学生,上午迟到时间段内打卡的次数
            sum(if(substring(signin_time, 12, 8)
                between substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', afternoon_begin_time) AS timestamp)) as varchar), 12,8)
                       and substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', afternoon_end_time) AS timestamp)) as varchar),12, 8), 1, 0)) as late_signin_cnt_each_student
           from oe_dwb.dwb_clock_late_detail
           where content is not null
             and content <> ''
           group by class_id, class_date, student_id),
-- 对于每个班级,每个日期,如果下午正常出勤时间内有打卡,则判定为早上正常打卡
-- 对于每个班级,每个日期,如果下午正常出勤时间内没有打卡,且迟到时间段内有打卡,则判定为早上迟到打卡
    t4 as (select class_id,
               class_date,
               'a' as period,
               -- 对于每个班级,每个日期,如果早上正常出勤时间内有打卡,则判定为早上正常打卡
               count(if(normal_signin_cnt_each_student > 0, 1, null)) as normal_signin_cnt,
               -- 对于每个班级,每个日期,如果早上正常出勤时间内没有打卡,且迟到时间段内有打卡,则判定为早上迟到打卡
               count(if(normal_signin_cnt_each_student = 0 and late_signin_cnt_each_student > 0, 1,null)) as late_signin_cnt
           from t3
           group by class_id, class_date),
-- 晚上正常出勤 请假 人数计算
    t5 as (select
            --维度
            class_id,
            class_date,
            student_id,


            --指标
            -- 每个班级,每个日期,每个学生,上午正常出勤时间段内打卡的次数
            sum(if(substring(signin_time, 12, 8)
                between substring(cast(date_add('minute', -40,cast(concat('1970-01-01 ', evening_begin_time) AS timestamp)) as varchar),12, 8)
                       and substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', evening_begin_time) AS timestamp)) as varchar),12, 8), 1, 0)) as normal_signin_cnt_each_student,
            -- 每个班级,每个日期,每个学生,上午迟到时间段内打卡的次数
            sum(if(substring(signin_time, 12, 8)
                between substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', evening_begin_time) AS timestamp)) as varchar), 12, 8)
                       and substring(cast(date_add('minute', 10,cast(concat('1970-01-01 ', evening_end_time) AS timestamp)) as varchar),12, 8), 1, 0)) as late_signin_cnt_each_student
           from oe_dwb.dwb_clock_late_detail
           where content is not null
             and content <> ''
           group by class_id, class_date, student_id),
-- 对于每个班级,每个日期,如果晚上正常出勤时间内有打卡,则判定为早上正常打卡
-- 对于每个班级,每个日期,如果晚上正常出勤时间内没有打卡,且迟到时间段内有打卡,则判定为早上迟到打卡
    t6 as (select class_id,
               class_date,
               'e'                                                    as period,
               -- 对于每个班级,每个日期,如果早上正常出勤时间内有打卡,则判定为早上正常打卡
               count(if(normal_signin_cnt_each_student > 0, 1, null)) as normal_signin_cnt,
               -- 对于每个班级,每个日期,如果早上正常出勤时间内没有打卡,且迟到时间段内有打卡,则判定为早上迟到打卡
               count(if(normal_signin_cnt_each_student = 0 and late_signin_cnt_each_student > 0, 1,
                        null))                                        as late_signin_cnt
        from t5
        group by class_id, class_date),
    sighin_cnt as (select *
                   from t2
                   union all
                   select *
                   from t4
                   union all
                   select *
                   from t6),
    t11 as (select class_id,
                class_date,
                student_id,
                sum(if(cast(concat(class_date, ' ', morning_begin_time) as varchar) <= substring(end_time, 1, 19)
                           and cast(concat(class_date, ' ', morning_end_time) as varchar) >= substring(begin_time, 1, 19),1,0)) as leave_student_judge
         from oe_dwb.dwb_leave_late_detail
         where content is not null
         and content <> ''
         group by class_id, class_date, student_id),
    t22 as (select class_id,
                        class_date,
                        'm'                                         as period,
                        count(if(leave_student_judge > 0, 1, null)) as leave_cnt
                 from t11
                 group by class_id, class_date),
    t33 as (select class_id,
                   class_date,
                   student_id,
                   sum(if(cast(concat(class_date, ' ', afternoon_begin_time) as varchar) <= substring(end_time, 1, 19)
                              and
                          cast(concat(class_date, ' ', afternoon_end_time) as varchar) >= substring(begin_time, 1, 19),
                          1,
                          0)) as leave_student_judge
            from oe_dwb.dwb_leave_late_detail
            where content is not null
            and content <> ''
            group by class_id, class_date, student_id),
    t44 as (select class_id,
                   class_date,
                   'a'                                         as period,
                   count(if(leave_student_judge > 0, 1, null)) as leave_cnt
            from t33
            group by class_id, class_date),
    t55 as (select class_id,
                   class_date,
                   student_id,
                   sum(if(cast(concat(class_date, ' ', evening_begin_time) as varchar) <= substring(end_time, 1, 19)
                              and
                          cast(concat(class_date, ' ', evening_end_time) as varchar) >= substring(begin_time, 1, 19),
                          1,
                          0)) as leave_student_judge
            from oe_dwb.dwb_leave_late_detail
            where content is not null
            and content <> ''
            group by class_id, class_date, student_id),
    t66 as (select class_id,
                   class_date,
                   'e'                                         as period,
                   count(if(leave_student_judge > 0, 1, null)) as leave_cnt
            from t55
            group by class_id, class_date),
    leave_cnt as (select *
                  from t22
                  union all
                  select *
                  from t44
                  union all
                  select *
                  from t66),
    unionall as (select coalesce(s.class_id, l.class_id)     as class_id,
                        coalesce(s.class_date, l.class_date) as class_date,
                        coalesce(s.period, l.period)         as period,
                        normal_signin_cnt,
                        late_signin_cnt,
                        leave_cnt
                 from sighin_cnt s
                          full join leave_cnt l
                                    on s.class_id = l.class_id and s.class_date = l.class_date and s.period = l.period)
select u.class_id,
       u.class_date,
       u.period,
       coalesce(u.normal_signin_cnt,0) as normal_signin_cnt,
       CAST(coalesce(cast(u.normal_signin_cnt as decimal(38, 5)) / NULLIF(c.studying_student_count, 0),0) AS DECIMAL(5, 2)) as normal_signin_rate,
       coalesce(u.late_signin_cnt,0) as late_signin_cnt,
       CAST(coalesce(CAST(u.late_signin_cnt AS DECIMAL(38, 5)) / NULLIF(c.studying_student_count, 0),0) AS DECIMAL(5, 2)) as late_signin_rate,
       coalesce(u.leave_cnt,0) as leave_cnt,
       CAST(coalesce(CAST(u.leave_cnt AS DECIMAL(38, 5)) / NULLIF(c.studying_student_count, 0),0) AS DECIMAL(5, 2)) as leave_rate,
       coalesce(c.studying_student_count - u.normal_signin_cnt - u.late_signin_cnt - u.leave_cnt,0) as absent_cnt,
       CAST(coalesce(CAST((c.studying_student_count - u.normal_signin_cnt - u.late_signin_cnt - u.leave_cnt) AS DECIMAL(38, 5)) / NULLIF(c.studying_student_count, 0 ),0)AS DECIMAL(5, 2)) AS absent_rate,

       '2024-08-23' AS DT
from unionall u
left join oe_dwd.fact_class_studying_student_count c on u.class_id = c.class_id and u.class_date = c.studying_date;
"